package phototagger;

import ireval.Main;
import java.awt.Image;
import java.io.BufferedOutputStream;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.xml.transform.TransformerException;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpression;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;
import org.w3c.dom.Document;
import org.w3c.dom.NodeList;

public class PhotoTaggingExperiments {

	// The Flickr API application ID (please change in order to use your own)
	private static String appid = "dfc172c6c2fc424c9ec45d6b8ba61288";
	static FileWriter judgments; 
	static FileWriter ranking;
	static BufferedWriter judgmentsOut; 
	static BufferedWriter rankingOut; 

	/**
	 * Populate the database with photos for test
	 */
	public static void trainingPhotosToDatabase() throws SQLException, IOException{
		
		judgments = new FileWriter("C:\\Users\\Filipa\\Desktop\\judgments.txt"); 
		judgmentsOut = new BufferedWriter(judgments);
		
		Connection connection = DriverManager.getConnection(
				"jdbc:postgresql:template_postgis", "postgres", "db123");
		Statement statement = connection.createStatement();		
    	File folder = new File("C:\\Users\\Filipa\\Desktop\\Fotos");
    	File[] listOfFiles = folder.listFiles();
    	List<String> point = new ArrayList<String>();
    	List<String> tags = new ArrayList<String>();
    	String [] fileName;
    	String url, query, coordinate, tag, url_photo;
    	int position;
    	Document document;
		
	    // For each file, get the photo_id
    	for (int i = 0; i < listOfFiles.length; i++) {
			fileName = listOfFiles[i].getName().split("_");
			if (Character.isDigit(fileName[0].charAt(1)))
				position = 0;
			else
				position = 2;
			
			try {
				// Get the photo location through the photo_id
				url = "http://api.flickr.com/services/rest/?method=flickr.photos.geo.getLocation&api_key=" + appid + "&photo_id=" + fileName[position];
		        document = QueryFlickr.httpGet(url); 
				point = coordinateFromPhoto(document);
		        coordinate = "POINT(" + point.get(0) + " " + point.get(1) + ")";
		        System.out.println(coordinate);
		        
			    // Get the tags through the photo_id
			    url = "http://api.flickr.com/services/rest/?method=flickr.photos.getInfo&api_key=" + appid + "&photo_id=" + fileName[position];
			    document = QueryFlickr.httpGet(url);
			    tags =  tagsFromPhotos(document);
		        
			   // Get the photo's url 
			    url = "http://api.flickr.com/services/rest/?method=flickr.photos.getSizes&api_key=" + appid + "&photo_id=" + fileName[position];
			    document = QueryFlickr.httpGet(url);
				url_photo = urlFromPhotos(document);
				
			} catch (XPathExpressionException e) {
				System.out.println(e.getMessage());
				continue;
			} catch (Exception e) {
				System.out.println(e.getMessage());
				continue;
			}
 
	       // Database submission 
	       Iterator<String> iterator = tags.iterator();     
	       while (iterator.hasNext()){
	    	   tag = iterator.next();
	    	   query = "SELECT * FROM insertTrainingPhotos('" + fileName[position] + "', '" + tag + "', '" + coordinate + "', '" + url_photo + "')";
	    	   System.out.println(query);
	    	   statement.executeQuery(query);
	    	   judgmentsOut.write(fileName[position]+ " 0 " + tag + " 1");
	    	   judgmentsOut.newLine();
	       }
		}
    	judgmentsOut.close();
		statement.close();
		connection.close();	
	}

	/**
	 * Get the photo's url
	 */
	public static String urlFromPhotos(Document document) throws XPathExpressionException{
		String url;		
		String xpathStr = "//size";
		XPathExpression expr;
		NodeList elemList = null;
		XPath xpath = null;
		
		xpath = XPathFactory.newInstance().newXPath();
		expr = xpath.compile(xpathStr);
		elemList = (NodeList) expr.evaluate(document, XPathConstants.NODESET);
		url = xpath.compile("./@source").evaluate(elemList.item(0));

		return url;	
	}
	
	
	/**
	 * Get near photos from each test photo 
	 */
	public static void nearPhotosToDatabase() throws SQLException, XPathExpressionException, TransformerException{
		
		String query, originalPhotoID, originalCoordinate, url;
		double latitude, longitude;
		Connection connection = DriverManager.getConnection(
				"jdbc:postgresql:template_postgis", "postgres", "db123");
		Statement statement1 = connection.createStatement();
		Statement statement2 = connection.createStatement();		
		query = "select * from getCoordinates()";
    	ResultSet result = statement1.executeQuery(query);
    	 
   	 	while (result.next()){
   	 		originalPhotoID = result.getString(1);
   	 		longitude = result.getDouble(2);
   	 		latitude = result.getDouble(3);
   	 		url = result.getString(4);
   	 		Document photosByRadius = QueryFlickr.queryFlickr(longitude, latitude, 32);
   	 		originalCoordinate = "POINT(" + longitude + " " + latitude + ")";
   	 		nearPhotosSubmission(photosByRadius, statement2, originalPhotoID, originalCoordinate, url);
   	 	}	
   	 	
   	 	result.close();
		statement1.close();
		statement2.close();
		connection.close(); 	
	}

	
	/**
	 * Takes the results of the near photos and submite them to the database
	 */
	public static void nearPhotosSubmission(Document doc, Statement connection, String originalPhotoID, String originalCoordinate, String originalUrl) throws XPathExpressionException, SQLException, TransformerException {

		String photoID, userId, latitude, longitude, coordinate, query, id, url, views;
		String xpathStr = "//photo";
		XPathExpression expr;
		NodeList elemList;
		XPath xpath;
		String tags[];
		int comments, favorites; 
		double similaridade;
		Image originalPhoto, photo;
		
		xpath = XPathFactory.newInstance().newXPath();
		expr = xpath.compile(xpathStr);
		elemList = (NodeList) expr.evaluate(doc, XPathConstants.NODESET);	

		for (int i = 0; i < elemList.getLength(); i++) {			
			id = xpath.compile("./@url_o").evaluate(elemList.item(i));
			if (id.trim().length() == 0) continue;
			photoID = xpath.compile("./@id").evaluate(elemList.item(i));
			userId = xpath.compile("./@owner").evaluate(elemList.item(i));
			url = xpath.compile("./@url_o").evaluate(elemList.item(i));
			views = xpath.compile("./@views").evaluate(elemList.item(i));
			latitude = xpath.compile("./@latitude").evaluate(elemList.item(i));
			longitude = xpath.compile("./@longitude").evaluate(elemList.item(i));
			coordinate = "POINT(" + longitude + " " + latitude + ")";
			tags = xpath.compile("./@tags").evaluate(elemList.item(i)).split(" +");
			comments = getPhotoComments(photoID);
			favorites = getPhotoFavorites(photoID);
			originalPhoto = QueryFlickr.getPhoto(originalUrl);
			photo = QueryFlickr.getPhoto(url);
			System.out.println(photoID);
			similaridade = QueryFlickr.imageSimilarity(originalPhoto, photo);
			System.out.println(similaridade);
 			query = "SELECT * FROM insertPhotosSimilarity('" + originalPhotoID + "', '" + photoID + "', '" + similaridade + "', '" + originalCoordinate + "', '" + coordinate + "' )";
 			connection.executeQuery(query);
 			
 			for (String tag : tags) {
				if (!QueryFlickr.isValidTag(tag))continue;
				query = "SELECT * FROM insertNearPhotos('" + userId + "', '" + photoID + "', '" + tag + "', '" + coordinate + "', '" + originalPhotoID + "', '" + url + "', '" + views + "', '" + comments + "', '" + favorites + "' )";
				System.out.println(query);
				connection.executeQuery(query);
			}
		}
	}
	
	/**
	 * Retrieve the comments relatively to a photo
	 */
	public static int getPhotoComments(String photoID) throws XPathExpressionException{
		
		String url = "http://api.flickr.com/services/rest/?method=flickr.photos.comments.getList&api_key=" + appid + "&photo_id=" + photoID;
		String xpathStr = "//comment";
		XPathExpression expr;
		NodeList elemList = null;
		XPath xpath = null;
		Document document = QueryFlickr.httpGet(url);
		
		xpath = XPathFactory.newInstance().newXPath();
		expr = xpath.compile(xpathStr);
		elemList = (NodeList) expr.evaluate(document, XPathConstants.NODESET);	
		
		return elemList.getLength();		
	}
	
	/**
	 * Retrieve the favorites relatively to a photo
	 */
	public static int getPhotoFavorites(String photoID) throws XPathExpressionException{
		
		String url = "http://api.flickr.com/services/rest/?method=flickr.photos.getFavorites&api_key=" + appid + "&photo_id=" + photoID;
		String xpathStr = "//person";
		XPathExpression expr;
		NodeList elemList = null;
		XPath xpath = null;
		Document document = QueryFlickr.httpGet(url);
		
		xpath = XPathFactory.newInstance().newXPath();
		expr = xpath.compile(xpathStr);
		elemList = (NodeList) expr.evaluate(document, XPathConstants.NODESET);	
		
		return elemList.getLength();		
	}

	/**
	 * Takes a XML document representing a photo and retrieve the coordinates
	 */
	public static List<String> coordinateFromPhoto(Document document)
			throws XPathExpressionException, Exception {

		String xpathStr = "//location";
		XPath xpath = XPathFactory.newInstance().newXPath();
		XPathExpression expr = xpath.compile(xpathStr);
		NodeList elemList = (NodeList) expr.evaluate(document, XPathConstants.NODESET);
		List<String> list = new ArrayList<String>();

		for (int w = 0; w < elemList.getLength(); w++) {			
			list.add(xpath.compile("./@longitude").evaluate(elemList.item(w)));
			list.add(xpath.compile("./@latitude").evaluate(elemList.item(w)));
		}

		if (list.isEmpty())
			throw new Exception("Erro: não foi possível obter o ponto associado à foto.");
		
		return list;
	}

	 /**
	 * Takes a XML document representing a photo and retrieve the tags
	 */
	public static List<String> tagsFromPhotos(Document document)
			throws XPathExpressionException {

		String xpathStr = "//tag";
		XPath xpath = XPathFactory.newInstance().newXPath();
		XPathExpression expr = xpath.compile(xpathStr);
		NodeList elemList = (NodeList) expr.evaluate(document, XPathConstants.NODESET);
		List<String> list = new ArrayList<String>();

		for (int w = 0; w < elemList.getLength(); w++)
			list.add(xpath.compile(".").evaluate(elemList.item(w)));

		return list;
	}


	
	public static void photoTagging() throws SQLException, IOException{
		
		ranking = new FileWriter("C:\\Users\\Filipa\\Desktop\\ranking.txt");
		rankingOut = new BufferedWriter(ranking);
		
		Connection connection = DriverManager.getConnection(
				"jdbc:postgresql:template_postgis", "postgres", "db123");
		Statement statement = connection.createStatement();
		Statement statement2 = connection.createStatement();		
		Map<String, Integer> results = new HashMap<String, Integer>();
		String query, photoID;
		ResultSet result = null, result2 = null;
		double latitude, longitude;
		int radius = 1;
		query = "select * from getCoordinates()";			
		result = statement.executeQuery(query);	
		
		while (result.next()) {
			photoID = result.getString(1);
			longitude = result.getDouble(2);
			latitude = result.getDouble(3);
			System.out.println("PhotID: " + photoID + " Longitude: " + longitude + "  Latitude: " + latitude);
			tagsEstimation(latitude, longitude, radius, photoID);
			query = "select * from getCandidatos('POINT(" + longitude + " " + latitude + ")')";			
			result2 = statement2.executeQuery(query);
			while (result2.next()){ 
				results.put(photoID, result2.getInt(1));
				System.out.println(result2.getInt(1));
			}		
		}
		
		rankingOut.close();
		result.close();	
		statement.close();
		connection.close();
	}	
		
	 /**
	 * Returns a tag estimation of the photo 
	 */
	public static void tagsEstimation(double latitude, double longitude, int radius, String photoID)
			throws SQLException, IOException {

		Connection connection = DriverManager.getConnection(
				"jdbc:postgresql:template_postgis", "postgres", "db123");
		Connection connection2 = DriverManager.getConnection(
				"jdbc:postgresql:template_postgis", "postgres", "db123");
		Statement statement = connection.createStatement();
		Statement statement2 = connection2.createStatement();
		Set<String> tags = new HashSet<String>();
		ResultSet result1 = null;
		ResultSet result2 = null;
		ResultSetMetaData rsmd1;
		String tag;
		int colCount1;
		
		String query = "SELECT * FROM getBestTags('POINT(" + longitude + " " + latitude + ")'," + radius + ")";
		result1 = statement.executeQuery(query);
		rsmd1 = result1.getMetaData();
		colCount1 = rsmd1.getColumnCount();			
		
		while (result1.next()) {
			for (int i = 1; i <= colCount1; i++){
				tags.add(result1.getString(i));
				query = "SELECT * FROM recomendaTags('" + result1.getString(i) + "')";
				result2 = statement2.executeQuery(query);				
				while (result2.next())
						tags.add(result2.getString(1));					
			}
		}		
		
		Iterator<String> it = tags.iterator();
		while (it.hasNext()) {
		    tag = (String) it.next();
			rankingOut.write(photoID + " 0 " + tag + " " + 1 + " 1 " + "STANDARD");
			rankingOut.newLine();
			System.out.println(tag);
		}

		result1.close();
		
		if(result2 != null)
			result2.close();
		
		statement.close();
		connection.close();
	}
	
	// Main method to test the class
	public static void main(String args[]) throws Exception {

		System.setProperty(
				"org.apache.xerces.xni.parser.XMLParserConfiguration",
				"org.apache.xerces.parsers.StandardParserConfiguration");

	    /**
	     * Populate the database with photos for test
	     */
		trainingPhotosToDatabase();

	    /**
	     * Get near photos from each test photo 
	     */
		nearPhotosToDatabase();

	     /**
	     * Tag estimation
	     */
		photoTagging();
		
	     /**
	     * Evaluation metrics
	     */
		//String[] files = {"C:\\Users\\Filipa\\Desktop\\ranking.txt", "C:\\Users\\Filipa\\Desktop\\judgments.txt" };
		//Main.main(files); 
		
		/*System.setOut(
			    new PrintStream(
			      new BufferedOutputStream(
			        new FileOutputStream("C:\\Users\\Filipa\\Desktop\\results.txt"))));*/
		
	}

}